﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;

namespace test
{
    class QueryBase
    {
        protected string sqlString = "";

        //public QueryBase()
        //{
        //    this.sqlString = "";
        //}

        public QueryBase Select()
        {
            this.sqlString = "select";
            return this;
        }

        public QueryBase Select(Object[] area)
        {
            this.sqlString = "select ";
            string tmp = " ";
            for (int iter = 0; iter < area.Length; iter++)
            {
                tmp += area[iter] + ",";
            }
            this.sqlString += tmp.Substring(0, tmp.Length - 1);
            return this;
        }

        public QueryBase From(string tableName)
        {
            if (sqlString.Equals("select"))
               this.sqlString += " * from " + tableName;
            else
                this.sqlString += " from " + tableName;
            return this;
        }

        public QueryBase Into()
        {
            if (sqlString.Equals("select"))
                this.sqlString += " * INTO ";
            else
                this.sqlString += " INTO ";
            return this;
        }

        public QueryBase Distinct(string field)
        {
            this.sqlString = " distinct " + field;
                //this.sqlString.Replace("*", " distinct " + field);
            return this;
        }

        public QueryBase Top(int number)
        {
            this.sqlString += " top " + number;
            return this;
        }

        public QueryBase PERCENT()
        {
            this.sqlString += " PERCENT ";
            return this;
        }


        public QueryBase Where(string field)
        {
            this.sqlString += " where " + field;
            return this;
        }


        /// <summary>
        /// like 通配符
        ///  %	替代一个或多个字符
        ///  _	仅替代一个字符
        /// [charlist]	字符列中的任何单一字符
        /// [^charlist]
        /// 或者
        /// [!charlist]
        /// </summary>
        /// <param name="val"></param>
        /// <returns></returns>
        public QueryBase Like(object val)
        {
            this.sqlString += " like " + val.ToString();
            return this;
        }


        public QueryBase In(object[] vals)
        {
            string tmp = " in (";
            for (int iter = 0; iter < vals.Length; iter++)
            {
                tmp += vals[iter].ToString() + ",";
            }
            this.sqlString += tmp.Substring(0, tmp.Length - 1) + ")";
            return this;
        }


        public QueryBase Union()
        {
            this.sqlString += " UNION ";
            return this;
        }

        public QueryBase UnionAll()
        {
            this.sqlString += " UNION ALL ";
            return this;
        }
        
        public QueryBase As(string val)
        {
            this.sqlString += " as " + val;
            return this;
        }

        public QueryBase Between(object valBegin, object valEnd)
        {
            this.sqlString += 
                " between  " + valBegin.ToString() + " and " + valEnd.ToString();
            return this;
        }


        public QueryBase IsNull()
        {
            this.sqlString += " IS NULL ";
            return this;
        }

        public QueryBase IsNotNull()
        {
            this.sqlString += " IS NOT NULL ";
            return this;
        }

        public QueryBase Equals(object val)
        {
            this.sqlString += "=" + val.ToString();
            return this;
        }

        public QueryBase And(string field)
        {
            this.sqlString += " and " + field;
            return this;
        }

        public QueryBase Or(string field)
        {
            this.sqlString += " or " + field;
            return this;
        }

        public QueryBase Not()
        {
            this.sqlString += " not ";
            return this;
        }

        public QueryBase DESC()
        {
            this.sqlString += " DESC ";
            return this;
        }

        public QueryBase ASC()
        {
            this.sqlString += " ASC ";
            return this;
            
        }

        public QueryBase DESC(string field)
        {
            char[] tmp = this.sqlString.ToCharArray();
            int limit = this.sqlString.Length - 4;
            if ('A' == tmp[limit]
                || 'E' == tmp[limit])
            {
                this.sqlString += ",";
            }

            this.sqlString += field + " DESC ";
            return this;
        }

        public QueryBase ASC(string field)
        {
            char[] tmp = this.sqlString.ToCharArray();
            int limit = this.sqlString.Length - 4;
            if ('A' == tmp[limit]
                || 'E' == tmp[limit])
            {
                this.sqlString += ",";
            }

            this.sqlString += field + " ASC ";
            return this;

        }

        public QueryBase GroupBy(string field)
        {
            this.sqlString += " group by " + field;
            return this;
        }

        public QueryBase GroupBy(string[] field)
        {
            string tmp = " ";
            for (int iter = 0; iter < field.Length; iter++)
            {
                tmp += field[iter] + ",";
            }
            this.sqlString += tmp.Substring(0, tmp.Length - 1);
            return this;
        }

        public QueryBase OrderBy(string field)
        {
            this.sqlString += " order by " + field;
            return this;
        }

        public QueryBase OrderBy()
        {
            this.sqlString += " order by ";
            return this;
        }

        public QueryBase Having()
        {
            this.sqlString += " having ";
            return this;
        }


        public override string ToString()
        {
            return this.sqlString;
        }


        

    }//end QueryBase
}
